<?php
/**
 * Created by PhpStorm.
 * User: jwb
 * Date: 2020/5/9
 * Time: 12:29
 */

namespace App\Services;


use App\Models\Conversion;
use App\Models\Patent;
use Carbon\Carbon;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\DB;

class PatentStatisticService
{
    //年费统计
    public function yearFeeGroupState($on=[])
    {
        return $this->feeModel($on)
            ->select(DB::raw('sum(patent_fees.amount) as amount,count(*) as count,patent_fees.state as state'))
            ->groupBy('state')->get();
    }

    public function yearAmountTotal($on)
    {
        return $this->feeModel($on)
            ->sum(DB::raw('patent_fees.amount'));
    }

    public function feeModel($on=[])
    {
       return DB::table('patents')
            ->join('patent_fees', function(JoinClause  $join) use ($on){
                $join->on('patent_fees.patent_id', '=', 'patents.id')
                    ->when($on,function(\Illuminate\Database\Query\Builder $query) use ($on){
                        $query->when(isset($on['type']),function(\Illuminate\Database\Query\Builder $query) use ($on){
                            if(is_array($on['type'])){
                                $query->whereIn('patent_fees.type', $on['type']);
                            }else{
                                $query->where('patent_fees.type', $on['type']);
                            }
                        })->when(isset($on['year']),function(\Illuminate\Database\Query\Builder $query) use ($on){
                            $query->whereYear('patent_fees.last_time',$on['year']);
                        })->when(isset($on['state']),function(\Illuminate\Database\Query\Builder $query) use ($on){
                            $query->where('patent_fees.state',$on['state']);
                        });
                    });
            })
            //费用类型
            ->when(request()->patent_type,function(\Illuminate\Database\Query\Builder $query){
                $patent_type = is_array(request()->patent_type)?request()->patent_type:[request()->patent_type];
                $query->whereIn('patents.patent_type', $patent_type);
            })
            ->when(request()->legal_status,function(\Illuminate\Database\Query\Builder $query){
                $legal_status = is_array(request()->legal_status)?request()->legal_status:[request()->legal_status];
                $query->whereIn('patents.legal_status', $legal_status);
            })
            ->when(request()->apply_date,function(\Illuminate\Database\Query\Builder $query){
                $apply_date = request()->apply_date;
                if(!empty($apply_date['start'])){
                    $query->whereDate('patents.apply_date','>=', $apply_date['start']);
                }
                if(!empty($apply_date['end'])){
                    $query->whereDate('patents.apply_date','<=', $apply_date['end']);
                }
            })
            ->when(request()->only(['patent_name','inventor','patent_sn']),function(\Illuminate\Database\Query\Builder $query){
                $like = request()->only(['patent_name','inventor','patent_sn']);
                foreach ($like as $k=>$v){
                    $query->where('patents.'.$k,'like', '%'.$v.'%');
                }
            })
           ->when(request()->search,function(\Illuminate\Database\Query\Builder $query){
               $search = request()->search;
               $query->where('patents.inventor','like', '%'.$search.'%')
                   ->orWhere('patents.patent_sn','like', '%'.$search.'%')
                   ->orWhere('patents.patent_name','like', '%'.$search.'%')
                   ->orWhere('patents.applicant','like', '%'.$search.'%');
           });
       //__search__
    }
}
